package net.comm.oper.db;

import org.sakaiproject.db.api.iServiceSql;


/**
 * methods for accessing user data in a database.
 */
public class NotAdvServiceSqlMysql implements iServiceSql
{
	/**
	 * 返回根据主键删除语句
	 */
	public String getDeleteSql()
	{
		return "delete from NotAdv where seqID=? and follID=?";
	}

	/**
	 * 返回根据主键删除语句
	 */
	public String getDelbyseqIDSql()
	{
		return "delete from NotAdv where seqID=? ";
	}
	/**
	 * 返回插入语句
	 */
	public String getInsertSql()
	{
		return "insert into NotAdv (  seqID,  follID,  ntype,  title,	introduction,  content,  f_ID," +
				"  nsort, nsortID,  up,  status,  attachcnt,  replycnt, userID,  ip,  mtime " +
				") values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
	}
	
	/**
	 * 返回根据主键修改语句
	 */
	public String getUpdateSql()
	{
		return "update NotAdv  set  ntype=?,  title=?,	introduction=?,  content=?,  f_ID=?," +
				"  nsort=?, nsortID=?,  up=?,  status=?,  attachcnt=?,  replycnt=?, userID=?,  ip=?,  mtime=? " +
				"where seqID=? and follID=?";
	}

	/**
	 * 返回根据主键修改语句
	 */
	public String getUpdstsSql()
	{
		return "update NotAdv  set  up=?,  status=?,  attachcnt=?,  replycnt=? " +
				"where seqID=? and follID=?";
	}

	/**
	 * 返回根据主键查询语句
	 */
	public String getbykeySql()
	{
		return "select   seqID,  follID,  ntype,  title,	introduction,  content,  f_ID," +
				"  nsort, nsortID,  up,  status,  attachcnt,  replycnt, userID,  ip,  mtime " +
				"from NotAdv where seqID=? and follID=?";
	}
	/**
	 * 返回根据主键查询语句
	 */
	public String getMaxFollIDSql()
	{
		return "select   seqID,  follID,  ntype,  title, introduction,  content,  f_ID," +
				"  nsort, nsortID,  up,  status,  attachcnt,  replycnt, userID,  ip,  mtime " +
				"from NotAdv where seqID=? Order by follID desc ";
	}
	
	/**
	 * 查询按Ntype分组后的前n条记录
	 * return the sql statement which updates an external user id for a given user in the sakai_user_id_map table.
	 */
	public String getByGroupSql()
	{
	
		StringBuffer sb =  new StringBuffer("select n1.seqID,  n1.follID,  n1.ntype,  n1.title,	n1.introduction," +
				"  n1.content,  n1.f_ID, n1.nsort, n1.nsortID,  n1.up,  n1.status,  n1.attachcnt,  n1.replycnt," +
				" n1.userID,  n1.ip,  n1.mtime " +
				" from NotAdv n1 where  n1.Ntype in (?,?,?,?,?,?) and n1.nsort = ? and n1.NsortID=? " +
				"and n1.FollID = 0 and ? > " +
				" (select count(*) from NotAdv n2 where n2.Ntype=n1.Ntype " +
				" and n2.nsort = n1.nsort and n2.NsortID=n1.NsortID and n2.FollID = n1.FollID " +
				" and n2.seqID > n1.seqID) order by n1.Ntype, mtime desc ");
		return sb.toString();	
	}		
	
	/**
	 * return the sql statement which updates an external user id for a given user in the sakai_user_id_map table.
	 */
	public String getRecbyPageSql()
	{
	
		StringBuffer sb =  new StringBuffer("select  seqID,  follID,  ntype,  title,	introduction,  content,  f_ID," +
				"  nsort, nsortID,  up,  status,  attachcnt,  replycnt, userID,  ip,  mtime " +
				"from NotAdv ");
		
		sb.append(" where ntype=? and nsort=? and nsortID=? and FollID=0 Order by Mtime Desc ");
		sb.append(" limit ?, ? ");
				
		return sb.toString();	
	}	
	/**
	 * return the sql statement which updates an external user id for a given user in the sakai_user_id_map table.
	 */
	public String getRecbyShequSql()
	{
	
		StringBuffer sb =  new StringBuffer("select  seqID,  follID,  ntype,  title,	introduction,  content,  f_ID," +
				"  nsort, nsortID,  up,  status,  attachcnt,  replycnt, userID,  ip,  mtime " +
				"from NotAdv ");
		
		sb.append(" where nsort in (1,2) and ntype in(1,2) and (" +
				"NsortID in (select YeweiID from Yewei where VillageID = ? ) " +
				"or NsortID in (select WuyeID from Wuye where VillageID = ? ) ) ");
		sb.append(" limit ?  ");
				
		return sb.toString();	
	}	
	/**
	 * return the sql statement which updates an external user id for a given user in the sakai_user_id_map table.
	 */
	public String getRecSql()
	{
	
		StringBuffer sb =  new StringBuffer("select  seqID,  follID,  ntype,  title,	introduction,  content,  f_ID," +
				"  nsort, nsortID,  up,  status,  attachcnt,  replycnt, userID,  ip,  mtime " +
				"from NotAdv ");
		
		return sb.toString();	
	}	
	

	/**
	 * 返回所有记录数
	 */
	public String getCountSql1()
	{
		return "select count(*) as COUNT from NotAdv ";
	}


	/**
	 * 返回根据查询条件的记录数
	 */
	public String getCountSql2()
	{
		return "select count(*) as COUNT from NotAdv where ntype=? and nsort=? and  NsortID=? and FollID=0";
	}
	/**
	 * 返回所有记录数
	 */
	public String getCountSql3()
	{
		return "select count(*) as COUNT from NotAdv ";
	}

	/**
	 * @see net.comm.job.db.iServiceSql.impl.UserServiceSql#getMaxInputsForSelectWhereInQueries()
	 */
	public int getMaxInputsForSelectWhereInQueries() {
		// For Oracle, the maximum supported number of expressions in a list is 1000. 
		return 1000;
	}
	
	public String getDeleteSql1()
	{
		return "delete from NotAdv where seqID=? ";
	}
}
